﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    public class t_class_teacher_DAL
    {
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="teachers"></param>
        /// <returns></returns>
        public static int InsertTeachers(IList<t_class_tearcher> teachers)
        {
            StringBuilder insertBuilder = new StringBuilder();

            foreach (t_class_tearcher teacher in teachers)
            {
                insertBuilder.AppendLine(string.Format(
                    @"insert into t_class_tearchers values('{0}',{1},'{2}',(select ISNULL(MAX(dataTxtid),0) + 1  from t_class_tearchers where fileId = {3}));",
                    teacher.teacher,
                    teacher.fileid,
                    teacher.createtime.ToString("yyyy-MM-dd HH:mm:ss"),
                    teacher.fileid));
            }


            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString());
        }

        /// <summary>
        /// 获取某个excel文件里的老师信息(根据fileid)
        /// </summary>
        /// <param name="fileid">文件id</param>
        /// <returns></returns>
        public static List<t_class_tearcher> GetTeacherListWithFileId(int fileid)
        {
            string selectSQL = "select id,tearcher,fileid,createtime,dataTxtid from t_class_tearchers where fileId = @fileid order by datatxtid asc;";

            SqlParameter selectParam = new SqlParameter()
            {
                ParameterName = "@fileId",
                Value = fileid
            };

            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), System.Data.CommandType.Text, selectSQL, selectParam);

            if (ds == null || ds.Tables.Count == 0) return null;
            List<t_class_tearcher> result = new List<t_class_tearcher>();
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_tearcher teacher = new t_class_tearcher();
                teacher.id = Convert.ToInt32(row["id"]);
                teacher.teacher = row["tearcher"].ToString();
                teacher.fileid = Convert.ToInt32(row["fileid"]);
                teacher.createtime = Convert.ToDateTime(row["createtime"]);
                teacher.dataTxtid = Convert.ToInt32(row["dataTxtid"]);

                result.Add(teacher);
            }

            return result;
        }

        /// <summary>
        /// 获取老师的信息，用于不排课条件的限制页面的数据绑定
        /// </summary>
        /// <param name="fileid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        public static List<t_class_teacherBind_Info> GetTeachersInfoWhitFileId(
            int fileid,
            out int totalCount,
            int pageIndex = 0,
            int pageCount = 10
            )
        {
            /*
             select * from 
(select t.id as tid , t.tearcher as tname, tcc.coursename as cname, row_number() over (order by t.id asc) rn from 
((select id,tearcher,fileId,createtime from t_class_tearchers where fileId = 1) t 
left join t_class_tearcher_course tctc 
on t.id = tctc.tearcherid 
left join t_class_course tcc 
on tctc.courseid = tcc.id)) tmp
where rn between 1 and 10
             */

            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.AppendLine("select * from ");
            selectBuilder.AppendLine("(select t.id as tid , t.tearcher as tname, tcc.coursename as cname, tcc.id as cid, row_number() over (order by t.id asc) rn from  ");
            selectBuilder.AppendLine("((select id,tearcher,fileId,createtime from t_class_tearchers where fileId = @fileid) t ");
            selectBuilder.AppendLine("left join t_class_tearcher_course tctc ");
            selectBuilder.AppendLine("on t.id = tctc.tearcherid ");
            selectBuilder.AppendLine("left join t_class_course tcc ");
            selectBuilder.AppendLine("on tctc.courseid = tcc.id)) tmp");
            selectBuilder.AppendLine("where rn between @begin and @end;");


            // 记录总数
            selectBuilder.AppendLine("select COUNT(1) as count from ((select id from t_class_tearchers where fileId = @fileid) t left join t_class_tearcher_course tctc on t.id = tctc.tearcherid left join t_class_course tcc on tctc.courseid = tcc.id);");

            int begin = pageIndex * pageCount + 1; // 0 * 10 + 1 = 1
            int end = (pageIndex + 1) * pageCount; // (0 + 1) * 10 = 10


            SqlParameter[] selectParams = new SqlParameter[] 
            {
                new SqlParameter("@fileid",fileid),
                new SqlParameter("@begin",begin),
                new SqlParameter("@end",end)
            };


            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, selectBuilder.ToString(), selectParams);

            if (ds == null || ds.Tables.Count == 0)
            {
                totalCount = 0;
                return null;
            }

            List<t_class_teacherBind_Info> bindList = new List<t_class_teacherBind_Info>();
            // 表一，查询结果集
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow row in rows)
            {
                t_class_teacherBind_Info bind = new t_class_teacherBind_Info();
                bind.tid = Convert.ToInt32(row["tid"]);
                bind.tname = row["tname"].ToString();
                bind.cname = row["cname"].ToString();
                bind.cid = Convert.ToInt32(row["cid"]);

                bindList.Add(bind);
            }


            // 表二，总数
            totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return bindList;
        }


        /// <summary>
        /// 获取老师信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static t_class_tearcher GetTeacherInfoById(int id)
        {
            string selectSQL = "select id,tearcher,fileid,createtime,datatxtid from t_class_tearchers where id = @id;";
            SqlParameter selectParam = new SqlParameter() 
            {
                ParameterName = "@id",
                Value = id
            };

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), CommandType.Text, selectSQL, selectParam);
            if (!reader.Read()) return null;

            var teacherInfo = new t_class_tearcher();
            teacherInfo.id = Convert.ToInt32(reader["id"]);
            teacherInfo.teacher = reader["tearcher"].ToString();
            teacherInfo.fileid = Convert.ToInt32(reader["fileid"]);
            teacherInfo.createtime = Convert.ToDateTime(reader["createtime"]);
            teacherInfo.dataTxtid = Convert.ToInt32(reader["datatxtid"]);

            reader.Close();

            return teacherInfo;
        }


        /// <summary>
        /// 根据fileid获取老师的数量
        /// </summary>
        /// <param name="fileid"></param>
        /// <returns></returns>
        public static int GetTeacherCountWithField(int fileid)
        {
            string selectCountSQL = "select COUNT(1) from t_class_tearchers where fileId = @fileid;";
            SqlParameter selectCountParam = new SqlParameter() 
            {
                ParameterName = @"fileid",
                Value = fileid
            };

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), CommandType.Text,selectCountSQL,selectCountParam));
        }


    }
}
